#!/usr/bin/env python
# -*- coding:utf-8 -*-
from typing import List

from excel.LFREE.LfreeDictUtil import LfreeDictUtil as DictUtil, LfreeDictUtil
from excel.LFREE.LfreeObj import LfreeObj
from excel.PruTuutDb import PruTuutDb

pru_tuut_db_conn = PruTuutDb.get_pool()
dict_db = DictUtil(pru_tuut_db_conn)


class LfreeBaseTabTop:

    def __init__(self):
        return

    # 计算 premium index 条件识别 LFREE_M_NS, LFREE_M_S, LFREE_F_NS, LFREE_F_S 数据表
    # Params:
    # 性別 C4 : Gender
    # 吸煙者 C5 : Smoker
    @staticmethod
    def get_premium_index(gender, smoker):
        # 参考计算公式
        # dataL!DZ108+IF(C4="M",0,8)+IF(C5="N",0,4)+IF(C8=1,0,IF(C8=10,1,IF(C8=20,2,3)))
        # DZ108 = LfreeDictUtil.loadDataL("DZ", 108)
        # cal_C4 = 0 if str(gender).upper() == "M" else 8
        # cal_C5 = 0 if str(smoker).upper() == "N" else 4
        # dict_C8 = LfreeObj.switch_C8_dict(payment_term)
        if str(gender).upper() == "M":
            return LfreeObj.GENDER_M_TERM[str(smoker).upper()]
        else:
            return LfreeObj.GENDER_F_TERM[str(smoker).upper()]

    # 计算 premium index 条件识别 1Y,10Y,20Y,30Y 数据索引
    # Params:
    # 供款期 C8 : Payment Term
    @staticmethod
    def get_mn_premium_index(payment_term):
        # 参考计算公式
        # dataL!DZ108+IF(C8=1,0,IF(C8=10,1,IF(C8=20,2,3)))
        return LfreeObj.switch_C8_dict[payment_term]

    # **
    # 计算 premium_ratio 获取保险费率
    # Params:
    # 投保額 C7 : sum_assured
    # 翌年歲 C3 : anb
    # 理论保额 C20 : data S.A.
    # 理论保额 C29 : discount
    def get_premium(self, anb, discount, sum_assured, args: List[str]):
        C4 = args[1]
        C5 = args[2]
        C8 = args[3]
        # 参考计算公式
        # IF(C3<76,(VLOOKUP(C3,dataL!A4:EO102,C20,TRUE)-C29)*C7/1000,0)
        if not anb < 76:
            return 0
        else:
            # STEP 1 VLOOKUP(C3, dataL!A4: EO102, C20, TRUE)
            # vlook_val = LfreeDictUtil.VLOOKUP(std=anb, area=["A4", "EO102"], target=C20)
            table_name = self.get_premium_index(gender=C4, smoker=C5)
            # anb_index = self.get_mn_premium_index(payment_term=C8) 这里需要做个修正，直接通过界面的供款期进行数据判断
            cal_value_C20 = dict_db.vlookup_fmns_by_anb([table_name, C8, anb])['data']
            # STEP 2 (vlook_val -C29)*C7 / 1000
            return (float(cal_value_C20) - discount) * sum_assured / 1000

    def get_mn_premium(self, C3, C21, C29, C7):
        # 参考计算公式
        # IF(C3<76,(VLOOKUP(C3, dataL!A4:EO102,C21,TRUE)-C29)*C7/1000,0)
        if not C3 < 76:
            return 0
        else:
            # STEP 1 VLOOKUP(C3, dataL!A4:EO102,C21,TRUE)
            vlook_val = LfreeDictUtil.VLOOKUP(std=C3, area=["A4", "EO102"], target=C21)
            # STEP 2 (vlook_val -C29)*C7/1000,0)
            return (vlook_val - C29) * C7 / 1000

    # **
    # 计算 premium_ratio 获取保险费率
    # Params:
    # 计算保险费 C22 : premium
    # 男性非吸烟者保险费 C23 : MN_premium
    # 年龄补偿 C32 : age_offset
    # 投保額 C7 : sum_assured
    # 翌年歲 C3 : anb
    # 理论保额 C30 : data S.A.
    # 理论保额 C24 : premium ratio
    def get_premium_ratio(self, args: List[str]):
        from decimal import Decimal
        # 参考计算公式
        # C22/C23
        # STEP CHECK ROW  1 [ C20 ==> premium index ]
        #                 2 [ C21 ==> MN premium index ]
        #                 3 [ C22 ==> IF($C$3<76,(VLOOKUP($C$3,dataL!$A$4:$EO$102,C20,TRUE)-C29)*$C$7/1000,0) ]
        #                 4 [ C23 ==> IF($C$3<76,(VLOOKUP($C$3,dataL!$A$4:$EO$102,C21,TRUE)-C29)*$C$7/1000,0) ]
        #                 5 [ C29 ==> discount ]
        anb = args[0]
        if not anb < 76:
            C22 = 0
            C23 = 0
        else:
            # **
            # 这里计算两次保费,吸烟者保费,非吸烟者保费
            C4 = args[1]
            C5 = args[2]
            C8 = args[3]
            C7 = args[4]
            try:
                # STEP 1 VLOOKUP($C$3,dataL!$A$4:$EO$102,C20,TRUE)
                table_name = self.get_premium_index(gender=C4, smoker=C5)
                anb_index = self.get_mn_premium_index(payment_term=C8)
                cal_value_C20 = dict_db.vlookup_fmns_by_anb([table_name, anb_index, anb])
                # STEP 2 VLOOKUP($C$3,dataL!$A$4:$EO$102,C21,TRUE)
                table_name_MN = self.get_premium_index(gender="M", smoker="N")
                anb_index_MN = self.get_mn_premium_index(payment_term=C8)
                cal_value_C21 = dict_db.vlookup_fmns_by_anb([table_name_MN, anb_index_MN, anb])
                # STEP 3 (cal_value-C29)*$C$7/1000
                C29 = self.get_discount(payment_term=C8, sum_assured=C7)
                C22 = (cal_value_C20['data'] - Decimal(C29)) * Decimal(C7) / 1000
                C23 = (cal_value_C21['data'] - Decimal(C29)) * Decimal(C7) / 1000
                premium_ratio = C22 / C23
            except ZeroDivisionError:
                return 0
        return round(float(premium_ratio), 4)

    # **
    # 计算 gcv_base
    # Params:
    # 投保額 C7 : sum_assured
    def get_lfree_discount_1y(self, sum_assured):
        # 参考计算公式
        # IF(C7>=250000,138.22,IF(C7>=125000,119,IF(C7>=75000,111,IF(C7>=50000,103,IF(C7>=30000,70,IF(C7>=20000,26,0))))))
        if sum_assured >= 250000:
            return 138.22
        if sum_assured >= 125000:
            return 119
        if sum_assured >= 75000:
            return 111
        if sum_assured >= 50000:
            return 103
        if sum_assured >= 30000:
            return 70
        if sum_assured >= 20000:
            return 26
        return 0

    def get_lfree_discount_10y(self, sum_assured):
        # 参考计算公式
        # IF(C7>=250000,15.8,IF(C7>=125000,15.5,IF(C7>=75000,15.1,IF(C7>=50000,14.7,IF(C7>=30000,9.95,IF(C7>=20000,5.2,0))))))
        if sum_assured >= 250000:
            return 15.8
        if sum_assured >= 125000:
            return 15.5
        if sum_assured >= 75000:
            return 15.1
        if sum_assured >= 50000:
            return 14.7
        if sum_assured >= 30000:
            return 9.95
        if sum_assured >= 20000:
            return 5.2
        return 0

    def get_lfree_discount_20y(self, sum_assured):
        # 参考计算公式
        # IF(C7>=250000,8.92,IF(C7>=125000,8.72,IF(C7>=75000,8.42,IF(C7>=50000,8.12,IF(C7>=30000,5.56,IF(C7>=20000,3,0))))))
        if sum_assured >= 250000:
            return 8.92
        if sum_assured >= 125000:
            return 8.72
        if sum_assured >= 75000:
            return 8.42
        if sum_assured >= 50000:
            return 8.12
        if sum_assured >= 30000:
            return 5.56
        if sum_assured >= 20000:
            return 3
        return 0

    def get_lfree_discount_30y(self, sum_assured):
        # 参考计算公式
        # IF(C7>=250000,6.68,IF(C7>=125000,6.48,IF(C7>=75000,6.28,IF(C7>=50000,5.98,IF(C7>=30000,4.09,IF(C7>=20000,2.2,0))))))
        if sum_assured >= 250000:
            return 6.68
        if sum_assured >= 125000:
            return 6.48
        if sum_assured >= 75000:
            return 6.28
        if sum_assured >= 50000:
            return 5.98
        if sum_assured >= 30000:
            return 4.09
        if sum_assured >= 20000:
            return 2.2
        return 0

    # **
    # params:
    # C8 : 供款期
    # 供款期 C8 : Payment Term
    def get_discount(self, payment_term, sum_assured):
        # 参考计算公式
        # IF(C8=1,C25,IF(C8=10,C26,IF(C8=20,C27,C28)))
        if payment_term == 1:
            return self.get_lfree_discount_1y(sum_assured=sum_assured)
        elif payment_term == 10:
            return self.get_lfree_discount_10y(sum_assured=sum_assured)
        elif payment_term == 20:
            return self.get_lfree_discount_20y(sum_assured=sum_assured)
        else:
            return self.get_lfree_discount_30y(sum_assured=sum_assured)

    def get_data_s_a(self):
        # 参考计算公式
        # 000000
        return 000000

    # **
    # params:
    # 翌年歲 C3 : anb
    @staticmethod
    def get_base_anb(anb):
        # 参考计算公式
        # ROUNDDOWN((C3-1)/5,0)*5+1
        # STEP 1 ROUNDDOWN((C3-1)/5,0)
        cal_val = (anb - 1) // LfreeObj.LFREE_RATE_AROUND
        # STEP 2 cal_val*5+1
        return cal_val * 5 + 1

    # **
    # params:
    # 翌年歲 C3 : anb
    # 当年歲 C31 : base_anb
    @staticmethod
    def get_age_offset(anb, base_anb):
        # 参考计算公式
        # C3-C31
        return anb - base_anb

    def get_payment_term_index(self, C8):
        # 参考计算公式
        # IF(C8=1,dataL!B108,IF(C8=10,dataL!AH108,IF(C8=20,dataL!BN108,IF(C8=30,dataL!CT108,0))))
        if C8 == 1:
            return LfreeDictUtil("B", 108)
        elif C8 == 10:
            return LfreeDictUtil("AH", 108)
        elif C8 == 20:
            return LfreeDictUtil("BN", 108)
        else:
            return LfreeDictUtil("CT", 108)
        return 0

    def get_base_index(self, C31, C33):
        # 参考计算公式
        # (C31-1)/5+C33
        return (C31 - 1) / 5 + C33

    def get_rate_index(self, C34):
        # 参考计算公式
        # C34+16
        return C34 + 16

    def get_gcv_base_index(self, C34):
        # 参考计算公式
        # C34+144
        return C34 + 144

    def get_gcv_rate_index(self, C35):
        # 参考计算公式
        # C35+144
        return C35 + 144

    # **
    # params:
    # 保险费 C22 : premium
    def get_lfree_annual_premium(self, premium):
        # 参考计算公式
        # C22
        return premium

    def get_lfree_annual_premium_hkd(self, C44, C39):
        # 参考计算公式
        # IF(C44=1,C39*7.8,C39)
        return C39 * 7.8 if C44 == 1 else C39

    def get_adjusted_lfree_cv_col(self, AK59, B59):
        # 参考计算公式
        # COLUMN(AK59)-COLUMN(B59)+1
        return 36

    def get_chi(self, B164):
        # 参考计算公式
        # IF(LOWER(B164)="chi","Yes","No")
        return "Yes" if B164 == "chi" else "No"

    # **
    # params:
    # 貨幣(USD/HKD-U/RMB-U) C10 : Currency
    def get_exchange_rate(self, currency):
        # 参考计算公式
        # IF(LOWER(C10)="hkd",8,1)
        return 8 if str(currency).lower() == "hkd" else 1

    # **
    # params:
    # 貨幣(USD/HKD-U/RMB-U) C10 : Currency
    def get_display_rate(self, currency):
        # 参考计算公式
        # IF(LOWER(C10)="rmb-u",Notes!B2,IF(LOWER(C10)="hkd-u",7.8,1))
        if str(currency).lower() == "rmb":
            return LfreeObj.USD_2_RMB_RATE
        elif str(currency).lower() == "hkd":
            return LfreeObj.HKD_2_RMB_RATE
        return LfreeObj.USD_RATE

    def get_underlying_currency(self, C10):
        # 参考计算公式
        # IF(LOWER(C10)="rmb-u","(USD)",IF(LOWER(C10)="hkd-u","(USD)",IF(LOWER(C10)="hkd","(HKD)",IF(LOWER(C10)="usd","(USD)"))))
        if str(C10).lower() == "rmb":
            return "(USD)"
        elif str(C10).lower() == "hkd":
            return "(HKD)"
        elif str(C10).lower() == "usd":
            return "(USD)"

    def get_display_currenty(self, C10):
        # 参考计算公式
        # IF(LOWER(C10)="rmb-u","(RMB)",IF(LOWER(C10)="hkd-u","(HKD)",IF(LOWER(C10)="hkd","(HKD)",IF(LOWER(C10)="usd","(USD)"))))
        return LfreeObj.switch_C10_dict(C10)

    # **
    # params:
    # 汇率 C44 : Exchange rate
    def get_sa_minimum(self, exchange_rate):
        # 参考计算公式
        # IF(C44=1,10000,80000)
        return 10000 if exchange_rate == 1 else 80000

    def get_low_sa_message(self, C43):
        # 参考计算公式
        # IF(C43="Yes","保額過低","Sum Assured Too Low")
        return "保額過低" if C43 == "Yes" else "Sum Assured Too Low"

    def get_max_withdrawal_age(self, F3, F5, F8):
        # 参考计算公式
        # IF(F8=0,F3,F5+F8-1)
        return F3 if F8 == 0 else F5 + F8 - 1

    def get_surrender_value(self, C52):
        # 参考计算公式
        # VLOOKUP(C52,B61:AQ161,COLUMN(AQ161)-COLUMN(B61)+1,FALSE)
        # 释义 =======> 对应列 =======> 公式
        # bonus base =======> K62 =======> VLOOKUP(A62,dataL!A4:DY103,C34,TRUE)
        # bonus rate =======> L62 =======> VLOOKUP(A62,dataL!A4:DY103,C35,TRUE)
        # bonus =======> M61 =======> IF(L62>0,K62*L62^C32*C7/C30,0)
        # GCV value =======> Q61 =======> IF(P62>0,O62*P62^C32*C7/C30*C24,0)
        # CV =======> R61 =======> M61+Q61
        # LFREE CV =======> AJ61 =======> R61
        # Adjusted EGS CV =======> AK =======> AJ61
        # A. EGS CV 1 =======> AL =======> IF(C17>0,IF((AB61-C8+1)>0,VLOOKUP(B61-C8,B61:AK161,C41,TRUE)*(1+C18)^1,0),0)
        # A. EGS CV 2 =======> AM =======> IF(C17>1,IF((AB61-C8*2+1)>0,VLOOKUP(B61-C8*2,B61:AK161,C41,TRUE)*(1+C18)^2,0),0)
        # A. EGS CV 3 =======> AN =======> IF(C17>2,IF((AB61-C8*3+1)>0,VLOOKUP(B61-C8*3,B61:AK161,C41,TRUE)*(1+C18)^3,0),0)
        # A. EGS CV 4 =======> AO =======> IF(C17>3,IF((AB61-C8*4+1)>0,VLOOKUP(B61-C8*4,B61:AK161,C41,TRUE)*(1+C18)^4,0),0)
        # A. EGS CV 5 =======> AP =======> IF(C17>4,IF((AB61-C8*5+1)>0,VLOOKUP(B61-C8*5,B61:AK161,C41,TRUE)*(1+C18)^5,0),0)
        # Total A. EGS CV =======> AQ =======> SUM(AK61:AP61)
        return 0

    def get_seed_value(self, C53, F8, F3, F5):
        # 参考计算公式
        # C53/(IF(F8=0,F3-F5+1,F8))
        # STEP 1 IF(F8=0,F3-F5+1,F8)
        cal_val = F3 - F5 + 1 if F8 == 0 else F8
        # STEP 2 C53/ cal_val
        return C53 / cal_val

    def get_max_withdrawal(self, DS53, C45):
        # 参考计算公式
        # ROUNDDOWN(DS53*C45/1000,0)*1000
        return (DS53 * C45 // 1000) * 1000
